Imports System.Data.SqlClient
Imports Crystal.CommonDBManagement.BLL

Public Class cTemplateMapping

#Region "Declare"

    Dim _FormatName As String
    Dim _Date As Date
    Dim _UserId As Integer
    Dim _FormatId As Integer
    Dim _FileColumn As String
    Dim _DBFieldId As Integer
    Dim _displayname As String
    Dim _Status As String

#End Region

#Region "Properties"

    Public Property FormatName() As String
        Get
            Return _FormatName
        End Get
        Set(ByVal value As String)
            _FormatName = value
        End Set
    End Property

    Public Property Displayname() As String
        Get
            Return _displayname
        End Get
        Set(ByVal value As String)
            _displayname = value
        End Set
    End Property

    Public Property CdDate() As Date
        Get
            Return _Date
        End Get
        Set(ByVal value As Date)
            _Date = value
        End Set
    End Property

    Public Property UserId() As Integer
        Get
            Return _UserId
        End Get
        Set(ByVal value As Integer)
            _UserId = value
        End Set
    End Property

    Public Property FormatId() As Integer
        Get
            Return _FormatId
        End Get
        Set(ByVal value As Integer)
            _FormatId = value
        End Set
    End Property

    Public Property DBFieldId() As Integer
        Get
            Return _DBFieldId
        End Get
        Set(ByVal value As Integer)
            _DBFieldId = value
        End Set
    End Property

    Public Property FileColumn() As String
        Get
            Return _FileColumn
        End Get
        Set(ByVal value As String)
            _FileColumn = value
        End Set
    End Property

    Public Property Status() As String
        Get
            Return _Status
        End Get
        Set(ByVal value As String)
            _Status = value
        End Set
    End Property

#End Region


#Region "Transactions"

    ''' <summary>
    ''' To Insert New Investor Format
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function Insert() As ExecutionResult
        Const ARRAY_SIZE As Integer = 3
        Dim eReturnVal As ExecutionResult
        Dim param(ARRAY_SIZE) As SqlClient.SqlParameter
        'Try

        param(0) = New SqlParameter("@formatname", _FormatName)
        param(1) = New SqlParameter("@date", _Date)
        param(2) = New SqlParameter("@userid", _UserId)
        param(3) = New SqlParameter("@FormatId", SqlDbType.Int)
        param(3).Direction = ParameterDirection.Output
        param(3).Value = 0
        eReturnVal = CoreFunctions.Execute(strConfiguration, "Cp_Sp_INV_ADD_InvestorFormat", CommandType.StoredProcedure, ReturnTypes.NonQuery, param)
        Return eReturnVal
        'Catch ex As Exception
        '    Throw ex
        'Finally
        '    param = Nothing
        'End Try
    End Function

    ''' <summary>
    ''' To Select TemplateIDs and TemplateNames of the selected Source
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function GetTemplateNames(ByVal ESourceID As Integer) As IDataReader
        Dim eReturnVal As ExecutionResult = Nothing
        Dim param(0) As SqlClient.SqlParameter
        param(0) = New SqlParameter("@ESourceID", ESourceID)
        eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_TemplateName", CommandType.StoredProcedure, ReturnTypes.DataReader, param)
        Return CType(eReturnVal.ResultObject, IDataReader)
    End Function

    ''' <summary>
    ''' To Insert New Format Details of mapping
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function InsertInvestorFields() As ExecutionResult
        Const ARRAY_SIZE As Integer = 3
        Dim eReturnVal As ExecutionResult
        Dim param(ARRAY_SIZE) As SqlClient.SqlParameter
        'Try
        param(0) = New SqlParameter("@ETemplateID", _FormatId)
        param(1) = New SqlParameter("@TFieldName", _FileColumn)
        param(2) = New SqlParameter("@DBFieldID", _DBFieldId)
        param(3) = New SqlParameter("@FieldID", SqlDbType.Int)
        param(3).Direction = ParameterDirection.Output
        param(3).Value = 0
        eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_INV_AddTemplateFields", CommandType.StoredProcedure, ReturnTypes.NonQuery, param)
        Return eReturnVal
        'Catch ex As Exception
        '    Throw ex
        'Finally
        '    param = Nothing
        'End Try
    End Function

    ''' <summary>
    ''' To Select Mapped Fields List and get DataBase Fields ID and Staus 
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function SelectMapDataBaseFields() As ArrayList
        Dim ArrList As New ArrayList
        Dim eReturnVal As ExecutionResult = Nothing
        Dim idisplayID As Integer
        Const ARRAY_SIZE As Integer = 3
        Dim param(ARRAY_SIZE) As SqlClient.SqlParameter
        'Try

        param(0) = New SqlParameter("@ETemplateID", _FormatId)
        param(1) = New SqlParameter("@displayname", Displayname)
        param(2) = New SqlParameter("@DBFieldID", SqlDbType.Int)
        param(2).Direction = ParameterDirection.Output
        param(3) = New SqlParameter("@Status", SqlDbType.VarChar, 50)
        param(3).Direction = ParameterDirection.Output
        param(3).Value = ""
        eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_MapDataBaseFields", CommandType.StoredProcedure, ReturnTypes.NonQuery, param)
        idisplayID = eReturnVal.ResultParamenters(2).Value
        ArrList.Add(idisplayID)
        If Not eReturnVal.ResultParamenters(3).Value Is DBNull.Value Then
            'If eReturnVal.ResultParamenters(3).Value <> "" Then
            ArrList.Add(eReturnVal.ResultParamenters(3).Value)
            'End If
        Else
            ArrList.Add("")
        End If
        Return ArrList
        'Catch ex As Exception
        '    Throw ex
        'Finally
        '    eReturnVal = Nothing
        '    param = Nothing

        'End Try
    End Function

    ''' <summary>
    ''' To Delete an Selective Investor Mapped FormatId from cp_investorImportfield
    ''' </summary>
    ''' <param name="FormatID"></param>
    ''' <remarks></remarks>
    ''' 

    Public Sub DeleteInvestorMapping(ByVal FormatID As Integer)
        Dim eReturnVal As ExecutionResult = Nothing
        Const ARRAY_SIZE As Integer = 0
        Dim param(ARRAY_SIZE) As SqlClient.SqlParameter
        'Try
        param(0) = New SqlParameter("@ETemplateID", FormatID)
        eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_DeleteTemplate", CommandType.StoredProcedure, ReturnTypes.NonQuery, param)
        'Catch ex As Exception
        '    Throw ex
        'Finally
        '    param = Nothing
        'End Try
    End Sub

    ''' <summary>
    ''' To Get the Mandatory DataFields from cp_investorDataBaseFields
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function GetMandatoryFields(ByVal sourceTypeCode As String) As IDataReader
        Dim eReturnVal As ExecutionResult = Nothing
        Const ARRAY_SIZE As Integer = 0
        Dim param(ARRAY_SIZE) As SqlClient.SqlParameter
        param(0) = New SqlParameter("@sourceTypeCode", sourceTypeCode)
        'Try
        eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_MandatoryDatabaseFieldsBySourceCode", CommandType.StoredProcedure, ReturnTypes.DataReader, param)
        Return CType(eReturnVal.ResultObject, IDataReader)
    End Function

    ''' <summary>
    ''' To Get the DataFields from cp_investorDataBaseFields
    ''' </summary>
    ''' <param name="DatabaseFieldId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function GetInvestorDataBaseFieldName(ByVal DatabaseFieldId As Integer) As String
        Dim eReturnVal As ExecutionResult = Nothing
        Dim strDataBaseFieldName As String
        Const ARRAY_SIZE As Integer = 1
        ' Try

        Dim param(ARRAY_SIZE) As SqlClient.SqlParameter
        param(0) = New SqlParameter("@DatabaseFieldId", DatabaseFieldId)
        param(1) = New SqlParameter("@DisplayName ", SqlDbType.VarChar, 30)
        param(1).Direction = ParameterDirection.Output
        eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_AddDatabaseFields", CommandType.StoredProcedure, ReturnTypes.Scalar, param)
        strDataBaseFieldName = CType(eReturnVal.ResultParamenters(1).Value, String)
        Return strDataBaseFieldName
        'Catch ex As Exception
        '    Throw ex
        'End Try
    End Function

    ''' <summary>
    ''' To Check Mandatory fields Already Mapped
    ''' </summary>
    ''' <param name="FormatID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function CheckExistenceInvestorField(ByVal FormatID As Integer) As Boolean
        Dim eReturnVal As ExecutionResult = Nothing
        Dim boolFlag As Boolean
        Const ARRAY_SIZE As Integer = 1
        ' Try
        Dim param(ARRAY_SIZE) As SqlClient.SqlParameter
        param(0) = New SqlParameter("@FormatId", FormatID)
        param(1) = New SqlParameter("@CheckMandatoryFieldsAlreadyExist", SqlDbType.Bit)
        param(1).Direction = ParameterDirection.Output
        eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_IsTemplateMandatory", CommandType.StoredProcedure, ReturnTypes.Scalar, param)
        boolFlag = CType(eReturnVal.ResultParamenters(1).Value, Boolean)
        Return boolFlag
        'Catch ex As Exception
        '    Throw ex
        'End Try
    End Function

    Public Function GetLatestAddedTemplate(ByVal ETemplateID As String) As DataSet
        Const ARRAY_SIZE As Integer = 0
        Dim param(ARRAY_SIZE) As SqlClient.SqlParameter
        Dim eReturnVal As ExecutionResult = Nothing
        Dim dsReturn As DataSet = Nothing
        param(0) = New SqlParameter("@ETemplateID", ETemplateID)
        eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_GetAddedTemplate", CommandType.StoredProcedure, ReturnTypes.DataSet, param)
        dsReturn = CType(eReturnVal.ResultObject, DataSet)
        Return dsReturn
    End Function

    Public Function GetTemplateMapping(ByVal ETemplateID As Int16, ByVal SourceTypeCode As String) As DataTable
        Const ARRAY_SIZE As Integer = 1
        Dim param(ARRAY_SIZE) As SqlClient.SqlParameter
        Dim eReturnVal As ExecutionResult = Nothing
        Dim dtReturn As DataTable = Nothing
        param(0) = New SqlParameter("@ETemplateID", ETemplateID)
        param(1) = New SqlParameter("@SourceTypeCode", SourceTypeCode)
        eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_TMP_GetTemplateMappings", CommandType.StoredProcedure, ReturnTypes.DataSet, param)
        dtReturn = CType(eReturnVal.ResultObject, DataSet).Tables(0)
        Return dtReturn
    End Function

    Public Function ISInvestorMappingExist(ByVal FormatID As Integer) As Boolean
        Dim eReturnVal As ExecutionResult = Nothing
        Const ARRAY_SIZE As Integer = 0
        Dim param(ARRAY_SIZE) As SqlClient.SqlParameter
        'Try
        Dim dsReturn As DataSet = Nothing

        param(0) = New SqlParameter("@ETemplateID", FormatID)
        eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_GetTemplateDetail", CommandType.StoredProcedure, ReturnTypes.DataSet, param)
        dsReturn = CType(eReturnVal.ResultObject, DataSet)
        If dsReturn.Tables(0).Rows.Count > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function CheckSavedValue(ByVal ETemplateID As Integer, ByVal DBFieldID As String) As String
        Dim eReturnVal As ExecutionResult = Nothing
        Dim valus As String = String.Empty
        Const ARRAY_SIZE As Integer = 1
        Dim param(ARRAY_SIZE) As SqlClient.SqlParameter
        Dim dsReturn As DataSet = Nothing
        param(0) = New SqlParameter("@ETemplateID", ETemplateID)
        param(1) = New SqlParameter("@DBFieldID", DBFieldID)
        eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_IsTemplateExist", CommandType.StoredProcedure, ReturnTypes.DataSet, param)
        dsReturn = CType(eReturnVal.ResultObject, DataSet)
        If dsReturn.Tables(0).Rows.Count <> 0 Then
            valus = dsReturn.Tables(0).Rows(0)(0).ToString()
        Else
            Return valus = ""
        End If
        Return valus
    End Function

#End Region

End Class
